{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4712 errors when extracting #bedrooms"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "def extractBd(x):\n",
    "    val = x['facts and features']\n",
    "    max_idx = val.find(' bd')\n",
    "    if max_idx < 0:\n",
    "        max_idx = len(val)\n",
    "    s = val[:max_idx]\n",
    "\n",
    "    # find comma before\n",
    "    split_idx = s.rfind(',')\n",
    "    if split_idx < 0:\n",
    "        split_idx = 0\n",
    "    else:\n",
    "        split_idx += 2\n",
    "    r = s[split_idx:]\n",
    "    return int(r)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "ename": "ValueError",
     "evalue": "invalid literal for int() with base 10: '-- sqft'",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mValueError\u001b[0m                                Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-18-acd3677bda57>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m      1\u001b[0m \u001b[0;31m# case I: Studio apartments\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 2\u001b[0;31m \u001b[0mextractBd\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m{\u001b[0m\u001b[0;34m'facts and features'\u001b[0m \u001b[0;34m:\u001b[0m \u001b[0;34m'Studio , 1 ba , -- sqft'\u001b[0m\u001b[0;34m}\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      3\u001b[0m \u001b[0;31m# or extractBd({'facts and features' : 'Studio , 1 ba , 550 sqft'})\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m<ipython-input-17-ef8b8ee5f3d9>\u001b[0m in \u001b[0;36mextractBd\u001b[0;34m(x)\u001b[0m\n\u001b[1;32m     13\u001b[0m         \u001b[0msplit_idx\u001b[0m \u001b[0;34m+=\u001b[0m \u001b[0;36m2\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     14\u001b[0m     \u001b[0mr\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0ms\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0msplit_idx\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 15\u001b[0;31m     \u001b[0;32mreturn\u001b[0m \u001b[0mint\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mr\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;31mValueError\u001b[0m: invalid literal for int() with base 10: '-- sqft'"
     ]
    }
   ],
   "source": [
    "# case I: Studio apartments\n",
    "extractBd({'facts and features' : 'Studio , 1 ba , -- sqft'})\n",
    "# or extractBd({'facts and features' : 'Studio , 1 ba , 550 sqft'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "ename": "ValueError",
     "evalue": "invalid literal for int() with base 10: '00+'",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mValueError\u001b[0m                                Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-19-c2446b5c49b9>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m      1\u001b[0m \u001b[0;31m# case II: multiple apartments listed\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 2\u001b[0;31m \u001b[0mextractBd\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m{\u001b[0m\u001b[0;34m'facts and features'\u001b[0m \u001b[0;34m:\u001b[0m \u001b[0;34m'3 $2,800+'\u001b[0m\u001b[0;34m}\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;32m<ipython-input-17-ef8b8ee5f3d9>\u001b[0m in \u001b[0;36mextractBd\u001b[0;34m(x)\u001b[0m\n\u001b[1;32m     13\u001b[0m         \u001b[0msplit_idx\u001b[0m \u001b[0;34m+=\u001b[0m \u001b[0;36m2\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     14\u001b[0m     \u001b[0mr\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0ms\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0msplit_idx\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 15\u001b[0;31m     \u001b[0;32mreturn\u001b[0m \u001b[0mint\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mr\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;31mValueError\u001b[0m: invalid literal for int() with base 10: '00+'"
     ]
    }
   ],
   "source": [
    "# case II: multiple apartments listed\n",
    "extractBd({'facts and features' : '3 $2,800+'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "ename": "ValueError",
     "evalue": "invalid literal for int() with base 10: '--'",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mValueError\u001b[0m                                Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-20-3100d2dcdb6c>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m      1\u001b[0m \u001b[0;31m# case III: invalid data\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 2\u001b[0;31m \u001b[0mextractBd\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m{\u001b[0m\u001b[0;34m'facts and features'\u001b[0m \u001b[0;34m:\u001b[0m \u001b[0;34m'Price/sqft: -- , -- bds , -- ba , -- sqft'\u001b[0m\u001b[0;34m}\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;32m<ipython-input-17-ef8b8ee5f3d9>\u001b[0m in \u001b[0;36mextractBd\u001b[0;34m(x)\u001b[0m\n\u001b[1;32m     13\u001b[0m         \u001b[0msplit_idx\u001b[0m \u001b[0;34m+=\u001b[0m \u001b[0;36m2\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     14\u001b[0m     \u001b[0mr\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0ms\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0msplit_idx\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 15\u001b[0;31m     \u001b[0;32mreturn\u001b[0m \u001b[0mint\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mr\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;31mValueError\u001b[0m: invalid literal for int() with base 10: '--'"
     ]
    }
   ],
   "source": [
    "# case III: invalid data\n",
    "extractBd({'facts and features' : 'Price/sqft: -- , -- bds , -- ba , -- sqft'})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Issues:\n",
    "- forgot about Studio apartments\n",
    "- ill-formatted data with missing info"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Single error row when extracting zipcode"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "f = lambda x: '%05d' % int(x['postal_code'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "ename": "TypeError",
     "evalue": "'NoneType' object is not subscriptable",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mTypeError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-23-1bd9d91ae50b>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mf\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;32mNone\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;32m<ipython-input-22-d208a19811f1>\u001b[0m in \u001b[0;36m<lambda>\u001b[0;34m(x)\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mf\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;32mlambda\u001b[0m \u001b[0mx\u001b[0m\u001b[0;34m:\u001b[0m \u001b[0;34m'%05d'\u001b[0m \u001b[0;34m%\u001b[0m \u001b[0mint\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mx\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'postal_code'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;31mTypeError\u001b[0m: 'NoneType' object is not subscriptable"
     ]
    }
   ],
   "source": [
    "f(None)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "=> Issue: Forgot to check for NULL values!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 167 errors when extracting bathrooms"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "def extractBa(x):\n",
    "    val = x['facts and features']\n",
    "    max_idx = val.find(' ba')\n",
    "    if max_idx < 0:\n",
    "        max_idx = len(val)\n",
    "    s = val[:max_idx]\n",
    "\n",
    "    # find comma before\n",
    "    split_idx = s.rfind(',')\n",
    "    if split_idx < 0:\n",
    "        split_idx = 0\n",
    "    else:\n",
    "        split_idx += 2\n",
    "    r = s[split_idx:]\n",
    "    return int(r)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "ename": "ValueError",
     "evalue": "invalid literal for int() with base 10: '2.5'",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mValueError\u001b[0m                                Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-31-aab6ce94a03f>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m      5\u001b[0m \u001b[0;31m# Row 94 (OperatorID=100007, ecCode=ValueError):\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      6\u001b[0m \u001b[0;31m# ('Townhouse For Rent','44 Spruce St # 44','Winchester','MA',1890.00000,'$4,500/mo','4 bds , 2.5 ba , 2,500 sqft','https://www.zillow.com/homedetails/44-Spruce-St-44-Winchester-MA-01890/2102250563_zpid/')\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 7\u001b[0;31m \u001b[0mextractBa\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m{\u001b[0m\u001b[0;34m'facts and features'\u001b[0m \u001b[0;34m:\u001b[0m \u001b[0;34m'2 bds , 2.5 ba , 1,409 sqft'\u001b[0m\u001b[0;34m}\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;32m<ipython-input-26-3f7de010d3ed>\u001b[0m in \u001b[0;36mextractBa\u001b[0;34m(x)\u001b[0m\n\u001b[1;32m     13\u001b[0m         \u001b[0msplit_idx\u001b[0m \u001b[0;34m+=\u001b[0m \u001b[0;36m2\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     14\u001b[0m     \u001b[0mr\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0ms\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0msplit_idx\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 15\u001b[0;31m     \u001b[0;32mreturn\u001b[0m \u001b[0mint\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mr\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;31mValueError\u001b[0m: invalid literal for int() with base 10: '2.5'"
     ]
    }
   ],
   "source": [
    "# Row 92 (OperatorID=100007, ecCode=ValueError):\n",
    "# ('Townhouse For Rent','7 Conant Rd APT 58','WINCHESTER','MA',1890.00000,'$2,850/mo','2 bds , 2.5 ba , 1,409 sqft','https://www.zillow.com/homedetails/7-Conant-Rd-APT-58-Winchester-MA-01890/56541131_zpid/')\n",
    "# Row 93 (OperatorID=100007, ecCode=ValueError):\n",
    "# ('Townhouse For Rent','48 Spruce St','Winchester','MA',1890.00000,'$4,250/mo','4 bds , 3.5 ba , 2,000 sqft','https://www.zillow.com/homedetails/48-Spruce-St-Winchester-MA-01890/165961280_zpid/')\n",
    "# Row 94 (OperatorID=100007, ecCode=ValueError):\n",
    "# ('Townhouse For Rent','44 Spruce St # 44','Winchester','MA',1890.00000,'$4,500/mo','4 bds , 2.5 ba , 2,500 sqft','https://www.zillow.com/homedetails/44-Spruce-St-44-Winchester-MA-01890/2102250563_zpid/')\n",
    "extractBa({'facts and features' : '2 bds , 2.5 ba , 1,409 sqft'})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Issue:\n",
    "- forgot about half rooms, need to clean them explicitly!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 170 errors when extracting Sqft"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "def extractSqft(x):\n",
    "    val = x['facts and features']\n",
    "    max_idx = val.find(' sqft')\n",
    "    if max_idx < 0:\n",
    "        max_idx = len(val)\n",
    "    s = val[:max_idx]\n",
    "\n",
    "    split_idx = s.rfind('ba ,')\n",
    "    if split_idx < 0:\n",
    "        split_idx = 0\n",
    "    else:\n",
    "        split_idx += 5\n",
    "    r = s[split_idx:]\n",
    "    r = r.replace(',', '')\n",
    "    return int(r)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "# example: ('House For Rent','232 Washington St','WINCHESTER','MA',1890.00000,'$1,700/mo','2 bds , 1 ba , -- sqft','https://www.zillow.com/homedetails/232-Washington-St-Winchester-MA-01890/56541255_zpid/')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "ename": "ValueError",
     "evalue": "invalid literal for int() with base 10: '--'",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mValueError\u001b[0m                                Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-28-5e8025f7a36e>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mextractSqft\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m{\u001b[0m\u001b[0;34m'facts and features'\u001b[0m \u001b[0;34m:\u001b[0m \u001b[0;34m'2 bds , 1 ba , -- sqft'\u001b[0m\u001b[0;34m}\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;32m<ipython-input-25-169f4ce31710>\u001b[0m in \u001b[0;36mextractSqft\u001b[0;34m(x)\u001b[0m\n\u001b[1;32m     13\u001b[0m     \u001b[0mr\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0ms\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0msplit_idx\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     14\u001b[0m     \u001b[0mr\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mr\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mreplace\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m','\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m''\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 15\u001b[0;31m     \u001b[0;32mreturn\u001b[0m \u001b[0mint\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mr\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;31mValueError\u001b[0m: invalid literal for int() with base 10: '--'"
     ]
    }
   ],
   "source": [
    "extractSqft({'facts and features' : '2 bds , 1 ba , -- sqft'})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "   +------------+------+\n",
    "   | ValueError | 4712 |\n",
    "   +------------+------+\n",
    "|- filter\n",
    "|- withColumn\n",
    "|- filter\n",
    "|- withColumn\n",
    "   +-----------+---+\n",
    "   | TypeError | 1 |\n",
    "   +-----------+---+\n",
    "|- mapColumn\n",
    "|- withColumn\n",
    "   +------------+---+\n",
    "   | ValueError | 3 |\n",
    "   +------------+---+\n",
    "|- withColumn\n",
    "   +------------+-----+\n",
    "   | ValueError | 222 |\n",
    "   +------------+-----+"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# number of exception rows\n",
    "num_except_rows = 4712 + 1 + 3 + 222\n",
    "total_rows = 38570 # -1 for header"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "12.80269639616282"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "num_except_rows / total_rows * 100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
